# SELECT SUM(ddd) AS count_days, CASE WHEN kmnumber >= 0 AND kmnumber < 3 THEN '0~3' WHEN kmnumber >= 3 AND kmnumber < 5 THEN '3~5' WHEN kmnumber >= 5 AND kmnumber < 8 THEN '5~8' WHEN kmnumber >= 8 AND kmnumber < 11 THEN '8~11' WHEN
# kmnumber >= 11 AND kmnumber < 15 THEN '11~15'  ELSE '15+' END AS groupby_days FROM (     SELECT     SUM(1) AS ddd, kmnumber FROM test GROUP
# BY kmnumber ) AS aa GROUP BY groupby_days;

from pyecharts.faker import Faker
import numpy as np
from pyecharts import options as opts
from pyecharts.faker import Faker
from pyecharts.render import make_snapshot
from snapshot_phantomjs import snapshot

from pyecharts.charts import Pie

data2 = []
def test():
    global data2
    import pandas as pd
    from sqlalchemy import create_engine
    engine = create_engine('mysql+pymysql://root:123456@localhost:3306/pythondata')
    # 查询语句，选出employee表中的所有数据
    sql = ''' SELECT  CASE WHEN kmnumber >= 0 AND kmnumber < 3 THEN '0~3' WHEN kmnumber >= 3 AND kmnumber < 5 THEN '3~5' WHEN kmnumber >= 5 AND kmnumber < 8 THEN '5~8' WHEN kmnumber >= 8 AND kmnumber < 11 THEN '8~11' WHEN 
kmnumber >= 11 AND kmnumber < 15 THEN '11~15'  ELSE '15+' END AS groupby_days,SUM(ddd) AS count_days FROM (     SELECT     SUM(1) AS ddd, kmnumber FROM test GROUP
BY kmnumber ) AS aa GROUP BY groupby_days;'''
    # read_sql_query的两个参数: sql语句， 数据库连接
    city = pd.read_sql_query(sql, engine)
    data1 = np.array(city)  # 先将数据框转换为数组
    data2 = data1.tolist()  # 其次转换为列表
    print(data2)  # 以数组形式打出来方便看

def pie_chart() -> Pie:
    # ********* Begin *********#
    global data2
    pie = (
        Pie()
    .add("",data2,center=["40%", "50%"])
    .set_global_opts(
        title_opts=opts.TitleOpts(title="Pie-Legend 滚动"),
        legend_opts=opts.LegendOpts(type_="scroll", pos_left="80%", orient="vertical"),
    )
    .set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
    )
    # ********** End **********#
    return pie
test()
make_snapshot(snapshot, pie_chart().render("aaaaa111.html"), "student_answer.png") # 输出图片
